package ac.kr.ml.dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;




import ac.kr.ml.vo.FriendVo;
import ac.kr.ml.vo.WriteVo;

public class ContentListDao {
	
	String LOCALHOST = "partner.medialog.co.kr";
	public void insert(WriteVo vo){


		Connection conn = null;
		Statement stmt= null;
		PreparedStatement pstmt= null;

		try {
			Class.forName("com.mysql.jdbc.Driver");

			String dburl = "jdbc:mysql://"+LOCALHOST+":3306/freshman";
			conn = DriverManager.getConnection(dburl, "freshman", "freshman!$");

//			String dburl = "jdbc:mysql://localhost:3306/mlb";
//			conn = DriverManager.getConnection(dburl, "root", "admin");
			
			//stmt = conn.createStatement();
			
//			String sql = "insert into CONTENT (content, time, good, bad, reply_cont, cnt_writer) values ('"
//					+vo.getContent()+"', '"+vo.getTime()+"', '"
//					+vo.getGood()+"', '"+vo.getBad()+"', '"+vo.getCnt_writer()+"', '"
//					+vo.getReply_cont()+"')";
			
			String sql = "insert into CONTENT (content, time, good, bad, reply_cont, cnt_writer) values(?,?,?,?,?,?)";

			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, vo.getContent());
			pstmt.setString(2, vo.getTime());
			pstmt.setInt(3, vo.getGood());
			pstmt.setInt(4, vo.getBad());
			pstmt.setInt(5, vo.getReply_cont());
			pstmt.setInt(6, vo.getCnt_writer());
			
			pstmt.execute();			
			
			//stmt.executeUpdate(sql);

			//rs.close();


		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException ex){
			ex.printStackTrace();
		} finally{
			try{
				//stmt.close();
				pstmt.close();

			}catch(SQLException e){}
			if(conn != null){
				try{
					conn.close();
				}catch(SQLException e){}
			}

		}
	}


	//전체 content list	
	public List<WriteVo> getAllList(int key, List<FriendVo> friendList){

		List<WriteVo> list = new ArrayList<WriteVo>();
				
		Connection conn = null;
		Statement stmt= null;
		
		String friend = "where cnt_writer='"+key+"' ";
		if(friendList != null)
		{
			for (int i = 0; i < friendList.size(); i++) {
				friend +="or cnt_writer='"+friendList.get(i).getFmpID()+"' ";
//				if(i<friendList.size()-1){
//					friend+="or ";
//				}
			}			
		}

		System.out.println("friend : " +friend);
		try {
			Class.forName("com.mysql.jdbc.Driver");

			String dburl = "jdbc:mysql://"+LOCALHOST+":3306/freshman";
			conn = DriverManager.getConnection(dburl, "freshman", "freshman!$");


			stmt = conn.createStatement();

			String sql = "select * from CONTENT "+friend+" order by con_id desc";
			System.out.println("sql  : " +sql);
			ResultSet rs = stmt.executeQuery(sql);

			while(rs.next()){				
				WriteVo vo = new WriteVo();
				vo.setCon_id(rs.getInt(1));
				vo.setContent(rs.getString(2));
				vo.setTime(rs.getString(3));
				vo.setGood(rs.getInt(4));
				vo.setBad(rs.getInt(5));
				vo.setReply_cont(rs.getInt(6));
				vo.setCnt_writer(rs.getInt(7));
				list.add(vo);
			}
			if(list.size()==0){
				list = null;
			}
			rs.close();
			
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException ex){
			ex.printStackTrace();
		} finally{
			try{
				stmt.close();
			}catch(SQLException e){}
			if(conn != null){
				try{
					conn.close();
				}catch(SQLException e){}
			}

		}
		
		return list;
	}
	
	//특정 user 글
	public List<WriteVo> getListByKey(int key){

		List<WriteVo> list = new ArrayList<WriteVo>();
				
		Connection conn = null;
		Statement stmt= null;
				
		try {
			Class.forName("com.mysql.jdbc.Driver");

			String dburl = "jdbc:mysql://"+LOCALHOST+":3306/freshman";
			conn = DriverManager.getConnection(dburl, "freshman", "freshman!$");


			stmt = conn.createStatement();

			String sql = "select * from CONTENT where cnt_writer ='"+key+"' order by con_id desc";
			
			ResultSet rs = stmt.executeQuery(sql);

			while(rs.next()){				
				WriteVo vo = new WriteVo();
				vo.setCon_id(rs.getInt(1));
				vo.setContent(rs.getString(2));
				vo.setTime(rs.getString(3));
				vo.setGood(rs.getInt(4));
				vo.setBad(rs.getInt(5));
				vo.setReply_cont(rs.getInt(6));
				vo.setCnt_writer(rs.getInt(7));
				list.add(vo);
			}
			if(list.size()==0){
				list = null;
			}
			rs.close();
			
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException ex){
			ex.printStackTrace();
		} finally{
			try{
				stmt.close();
			}catch(SQLException e){}
			if(conn != null){
				try{
					conn.close();
				}catch(SQLException e){}
			}

		}
		
		return list;
	}
	
	//특정 content
	public List<WriteVo> getInsertMeList(int con_id, int key){

		List<WriteVo> list = new ArrayList<WriteVo>();

		Connection conn = null;
		Statement stmt= null;

		try {
			Class.forName("com.mysql.jdbc.Driver");

			String dburl = "jdbc:mysql://"+LOCALHOST+":3306/freshman";
			conn = DriverManager.getConnection(dburl, "freshman", "freshman!$");


			stmt = conn.createStatement();

			String sql = "select * from CONTENT where con_id>"+con_id+" and cnt_writer ='"+key+"'";

			ResultSet rs = stmt.executeQuery(sql);

			while(rs.next()){
				WriteVo vo = new WriteVo();
				vo.setCon_id(rs.getInt(1));
				vo.setContent(rs.getString(2));
				vo.setTime(rs.getString(3));
				vo.setGood(rs.getInt(4));
				vo.setBad(rs.getInt(5));
				vo.setReply_cont(rs.getInt(6));
				vo.setCnt_writer(rs.getInt(7));
				list.add(vo);
			}
			if(list.size()==0){
				list = null;
			}

			rs.close();

		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException ex){
			ex.printStackTrace();
		} finally{
			try{
				stmt.close();
			}catch(SQLException e){}
			if(conn != null){
				try{
					conn.close();
				}catch(SQLException e){}
			}

		}
		return list;
	}

	//특정 con_id 이상의 content list	
	public List<WriteVo> getInsertList(int con_id, List<FriendVo> friendList, int key){

		List<WriteVo> list = new ArrayList<WriteVo>();
				
		Connection conn = null;
		Statement stmt= null;
		
		String friend = "where (con_id >"+con_id +" and cnt_writer='"+key+"' ) ";
		if(friendList != null)
		{
			for (int i = 0; i < friendList.size(); i++) {
				friend +="or (con_id > "+con_id+" and cnt_writer='"+friendList.get(i).getFmpID()+"') ";
			}			
		}

		System.out.println("friend : " +friend);

		try {
			Class.forName("com.mysql.jdbc.Driver");

			String dburl = "jdbc:mysql://"+LOCALHOST+":3306/freshman";
			conn = DriverManager.getConnection(dburl, "freshman", "freshman!$");


			stmt = conn.createStatement();

			String sql = "select * from CONTENT "+friend + "order by con_id desc";

			ResultSet rs = stmt.executeQuery(sql);

			while(rs.next()){
				WriteVo vo = new WriteVo();
				vo.setCon_id(rs.getInt(1));
				vo.setContent(rs.getString(2));
				vo.setTime(rs.getString(3));
				vo.setGood(rs.getInt(4));
				vo.setBad(rs.getInt(5));
				vo.setReply_cont(rs.getInt(6));
				vo.setCnt_writer(rs.getInt(7));
				list.add(vo);
			}

			if(list.size()==0){
				list = null;
			}
			rs.close();

		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException ex){
			ex.printStackTrace();
		} finally{
			try{
				stmt.close();
			}catch(SQLException e){}
			if(conn != null){
				try{
					conn.close();
				}catch(SQLException e){}
			}

		}
		return list;
	}
	
	public int upGood(int con_id){


		int good=0;

		Connection conn = null;
		Statement stmt= null;

		try {
			Class.forName("com.mysql.jdbc.Driver");

			String dburl = "jdbc:mysql://"+LOCALHOST+":3306/freshman";
			conn = DriverManager.getConnection(dburl, "freshman", "freshman!$");

			stmt = conn.createStatement();

			String sql = "update CONTENT as m1, (select good from CONTENT where con_id = '" + con_id 
					+"') as m2 set m1.good = m2.good + 1 where m1.con_id ='"+con_id +"'";
			
			stmt.executeUpdate(sql);
			
			sql ="select good from CONTENT where con_id='"+con_id+"'";
			ResultSet rs = stmt.executeQuery(sql);
			
			while(rs.next()){
				good = rs.getInt(1);
			}


		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException ex){
			ex.printStackTrace();
		} finally{
			try{
				stmt.close();

			}catch(SQLException e){}
			if(conn != null){
				try{
					conn.close();
				}catch(SQLException e){}
			}

		}
		return good;
	}

	public int upBad(int con_id){

		int bad=0;

		Connection conn = null;
		Statement stmt= null;

		try {
			Class.forName("com.mysql.jdbc.Driver");

			String dburl = "jdbc:mysql://"+LOCALHOST+":3306/freshman";
			conn = DriverManager.getConnection(dburl, "freshman", "freshman!$");

			stmt = conn.createStatement();

			String sql = "update CONTENT as m1, (select bad from CONTENT where con_id = '" + con_id 
					+"') as m2 set m1.bad = m2.bad + 1 where m1.con_id ='"+con_id +"'";
			
			stmt.executeUpdate(sql);
			
			sql ="select bad from CONTENT where con_id='"+con_id+"'";
			ResultSet rs = stmt.executeQuery(sql);
			
			while(rs.next()){
				bad = rs.getInt(1);
			}



		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException ex){
			ex.printStackTrace();
		} finally{
			try{
				stmt.close();

			}catch(SQLException e){}
			if(conn != null){
				try{
					conn.close();
				}catch(SQLException e){}
			}
		}
		
		return bad;
		
	}

	public int upReplyCont(int con_id){

		int reply_cont=0;
		Connection conn = null;
		Statement stmt= null;

		try {
			Class.forName("com.mysql.jdbc.Driver");

			String dburl = "jdbc:mysql://"+LOCALHOST+":3306/freshman";
			conn = DriverManager.getConnection(dburl, "freshman", "freshman!$");
			
			stmt = conn.createStatement();
			
			String sql = "update CONTENT as m1, (select reply_cont from CONTENT where con_id = '" + con_id 
					+"') as m2 set m1.reply_cont = m2.reply_cont + 1 where m1.con_id ='"+con_id +"'";


			stmt.executeUpdate(sql);
			
			sql ="select reply_cont from CONTENT where con_id='"+con_id+"'";
			ResultSet rs = stmt.executeQuery(sql);
			
			while(rs.next()){
				reply_cont = rs.getInt(1);
			}
			
			rs.close();


		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException ex){
			ex.printStackTrace();
		} finally{
			try{
				stmt.close();

			}catch(SQLException e){}
			if(conn != null){
				try{
					conn.close();
				}catch(SQLException e){}
			}

		}
		
		return reply_cont;
		
	}

}
